CREATE PROCEDURE SP_UPDATE_ASSEMBLY_RELEASE_STATUS
(
  @AssemblyReleaseId INT
)

AS
BEGIN
		DECLARE @TRANSFER_LOT_COUNT INT
		DECLARE  @SHIPPED_LOT_COUNT INT

		SELECT @TRANSFER_LOT_COUNT = COUNT(*) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P 
				WHERE LT.LOT_ID = SL.LOT_ID  AND SL.STATUS = 'Close'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'Die Bank'
				AND SL.SL_NEXT_PROCESS <> 'DIE BANK' 
				AND LT.LOT_NUMBER IN (SELECT DISTINCT SHIPPED_LOT_NUMBER FROM ASSM_RELEASE_TABLE ART, Assm_Release_Lot_Table ARLT 
						WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID
							AND ART.STATUS Not In ('Cancelled' , 'Closed')
							AND ART.ASSEMBLY_RELEASE_ID = @AssemblyReleaseId )
		
		SELECT @SHIPPED_LOT_COUNT = COUNt(DISTINCT SHIPPED_LOT_NUMBER) FROM ASSM_RELEASE_TABLE ART, Assm_Release_Lot_Table ARLT 
						WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID
							AND ART.STATUS Not In ('Cancelled' , 'Closed')
							AND ART.ASSEMBLY_RELEASE_ID = @AssemblyReleaseId

		IF @SHIPPED_LOT_COUNT = @TRANSFER_LOT_COUNT
		BEGIN				
				UPDATE ASSM_RELEASE_TABLE SET Status = 'Closed' WHERE ASSEMBLY_RELEASE_ID = @AssemblyReleaseId AND STATUS Not In ('Cancelled' , 'Closed')
		END
END